package com.yangxk.ureport.provider.sql;

import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

// 开发者自己使用, 没有严格按照数据库操作来写, 只是简单实现功能
public class SqlOperation {

    private DataSource dataSource;


    public boolean checkExistByName(String filename) throws SQLException {
        String sql = "select id, `name` from ureport_file where name = ?";
        Connection connection = dataSource.getConnection();
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, filename);

        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            close(connection, rs, ps);
            return true;
        }

        close(connection, rs, ps);
        return false;
    }

    public UReportFileEntity getByName(String filename) throws SQLException {
        String sql = "select id, name, content, create_time, update_time from ureport_file where name = ?";
        Connection connection = dataSource.getConnection();
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, filename);

        ResultSet rs = ps.executeQuery();
        UReportFileEntity result = null;
        if (rs.next()) {
            result = new UReportFileEntity();
            result.setId(rs.getInt("id"));
            result.setName(rs.getString("name"));
            result.setContent(rs.getBytes("content"));
            result.setCreateTime(rs.getDate("create_time"));
            result.setUpdateTime(rs.getDate("update_time"));
        }

        close(connection, rs, ps);
        return result;
    }

    public List<UReportFileEntity> getList() throws SQLException {
        String sql = "select id, name, create_time, update_time from ureport_file";
        Connection connection = dataSource.getConnection();
        PreparedStatement ps = connection.prepareStatement(sql);

        ResultSet rs = ps.executeQuery();
        List<UReportFileEntity> res = new ArrayList<UReportFileEntity>();
        while (rs.next()) {
            UReportFileEntity u = new UReportFileEntity();
            u.setId(rs.getInt("id"));
            u.setName(rs.getString("name"));
            u.setCreateTime(rs.getDate("create_time"));
            u.setUpdateTime(rs.getDate("update_time"));

            res.add(u);
        }

        close(connection, rs, ps);
        return res;
    }

    public void saveReport(String name, String content) throws SQLException {
        if (checkExistByName(name)) {
            throw new RuntimeException("报表已存在");
        }

        String sql = "insert into ureport_file(name, content, create_time, update_time) values(?, ?, ?, ?)";
        Connection connection = dataSource.getConnection();
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, name);
        ps.setBytes(2, content.getBytes());
        ps.setDate(3, new java.sql.Date(System.currentTimeMillis()));
        ps.setDate(4, new java.sql.Date(System.currentTimeMillis()));

        ps.execute();

        close(connection, null, ps);
    }

    public void updateReport(int id, String name, String content) throws SQLException {
        String sql = "update ureport_file set content=?, update_time=? where id = ?";
        Connection connection = dataSource.getConnection();
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setBytes(1, content.getBytes());
        ps.setDate(2, new java.sql.Date(System.currentTimeMillis()));
        ps.setInt(3, id);

        ps.executeUpdate();
        close(connection, null, ps);
    }

    public void deleteByName(String name) throws SQLException {
        String sql = "delete from ureport_file where name = ?";
        Connection connection = dataSource.getConnection();
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, name);

        ps.execute();

        close(connection, null, ps);
    }

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    private void close(Object o) {
        if (o == null)
            return ;
        if (o instanceof ResultSet) {
            try {
                ((ResultSet)o).close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (o instanceof Statement) {
            try {
                ((Statement)o).close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (o instanceof Connection) {
            try {
                ((Connection)o).close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    private void close(Connection connection, ResultSet rs, Statement statement) {
        close(connection);
        close(rs);
        close(statement);
    }
}
